package com.ph.irp.sell.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.model.PSellSellorder;
import com.ph.irp.sell.dao.SellOrderDAO;
import com.ph.util.DBUtil;
import com.ph.util.DateUtil;


public class SellOrderDAOImpl implements SellOrderDAO {
	private ComboPooledDataSource dataSource;
	private List<PSellSellorder> sellOrderList = null;
	Connection con=null;
	PreparedStatement pStmt = null;
	ResultSet rs = null;
	/**
	 * 增加销售订单
	 */
	public void addSellOrder(PSellSellorder sellOrder) throws Throwable {
		try{
			sellOrder.setAuditingDate(null);
			StringBuilder sb = new StringBuilder();
			sb.append("insert into p_sell_sellorder(CustomerID,CreateDate,sellOrderCode,SourceBillType,currencyId,sellTypeId,Brief,DeliveryMethodID,deliveryAddressId,settlementId,settleDate,ManagerID,DepartmentID,BusinesserID,MakeBiller,Auditinger,auditingDate,IsAuditing,IsCancel,IsClosed)");
			sb.append("values(");
			sb.append(sellOrder.getCustomerID()).append(",");
			sb.append("'").append(sellOrder.getCreateDate()).append("',");
			sb.append("'").append(sellOrder.getSellOrderCode()).append("'").append(",");
			sb.append(sellOrder.getSourceBillType()).append(",");
			sb.append(sellOrder.getCurrencyId()).append(",");
			sb.append(sellOrder.getSellTypeId()).append(",");
			sb.append("'").append(sellOrder.getBrief()).append("'").append(",");
			if(sellOrder.getDeliverymethodID() == 0 )
				sb.append("null,");
			else
				sb.append(sellOrder.getDeliverymethodID()).append(",");
			if(sellOrder.getDeliveryAddressId()!=0)
			   sb.append(sellOrder.getDeliveryAddressId()).append(",");
			else
				sb.append("null,");
			if(sellOrder.getSettlementId()!=0)
				sb.append(sellOrder.getSettlementId()).append(",");
			else
				sb.append("null,");
			sb.append("'").append(sellOrder.getSettleDate()).append("',");
			if(sellOrder.getManagerID()!=0)
				sb.append(sellOrder.getManagerID()).append(",");
			else
				sb.append("null,");
			if(sellOrder.getDepartmentID()!=0)
				sb.append(sellOrder.getDepartmentID()).append(",");
			else
				sb.append("null,");
			if(sellOrder.getBusinesserID()!=0)
				sb.append(sellOrder.getBusinesserID()).append(",");
			else
				sb.append("null,");
			sb.append("'").append(sellOrder.getMakeBiller()).append("'").append(",");
			sb.append("'").append(sellOrder.getAuditinger()).append("'").append(",");
			if(sellOrder.getAuditingDate()==null)
				sb.append("null,");
			else
				sb.append("'").append(sellOrder.getAuditingDate()).append("',");
			sb.append(sellOrder.getIsAuditing()).append(",");
			sb.append(sellOrder.getIsCancel()).append(",");
			sb.append(sellOrder.getIsClosed());
			sb.append(")");
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
			con.commit();
		}
		catch(Exception e){
		throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}

}
	
	/**
	 * 根据销售订单code 得到销售订单的Id
	 */
	@Override
	public int getSellOrderIdByCode(String sellOrderCode) throws Throwable {
		int sellOrderId = 0;
		try{
			StringBuilder sb = new StringBuilder();
			sb.append("select sellOrderId from p_sell_sellorder where sellOrderCode=");
			sb.append("'").append(sellOrderCode).append("'");
			con = dataSource.getConnection();
		    pStmt = con.prepareStatement(sb.toString());
		    rs = pStmt.executeQuery();
		    rs.next();
		    sellOrderId = rs.getInt(1);
		}
		catch(Exception e){
			throw new Exception(e.getMessage());
		}
		finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return sellOrderId;
	}

/**
 * 查询所有销售订单
 */

	@Override
	public List<PSellSellorder> getAllSellOrder() throws Throwable {
		List<PSellSellorder> sellOrderList = new ArrayList<PSellSellorder>();
		try{
			Connection con=null;
			PreparedStatement pStmt = null;
			ResultSet rs = null;
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_sell_sellorder");
			con = this.dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			this.turnResultSetToList(sellOrderList, rs);
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());	
		}
		finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return sellOrderList;
	}
/**
 * 根据条件查询销售订单
 */
public List<PSellSellorder> getSellOrderByCondition(PSellSellorder sellOrderModel,String beginDate, String endDate) throws Throwable {
		List<PSellSellorder> sellOrderList = new ArrayList<PSellSellorder>();
		try{
			Connection con=null;
			PreparedStatement pStmt = null;
			ResultSet rs = null;
			StringBuilder sb = new StringBuilder();
			sb.append("select * from p_sell_sellorder where 1=1");
			if(sellOrderModel!=null)
			{
				if(sellOrderModel.getSellOrderCode()!=null || (!sellOrderModel.getSellOrderCode().trim().equals("")))
					{
					sb.append(" and sellOrderCode like '%").append(sellOrderModel.getSellOrderCode().trim()).append("%'");
					}
				
				if(sellOrderModel.getIsAuditing()!=2)
				{
					sb.append(" and IsAuditing =").append(sellOrderModel.getIsAuditing());
				}
				if(sellOrderModel.getCustomerID()!=0)
				{
					sb.append(" and CustomerID=").append(sellOrderModel.getCustomerID());
				}
				if(sellOrderModel.getSellTypeId() !=4)
				{
					sb.append(" and sellTypeId=").append(sellOrderModel.getSellTypeId());
				}
				if(sellOrderModel.getDeliverymethodID()!=0)
				{
					sb.append(" and DeliveryMethodID=").append(sellOrderModel.getDeliverymethodID());
				}
				if(sellOrderModel.getSettlementId()!=0)
				{
					sb.append(" and settlementId=").append(sellOrderModel.getSettlementId());
				}
				if (beginDate != null && !beginDate.trim().equals("")
						&& endDate != null && !endDate.trim().equals("")) {
					sb.append(" and CreateDate between ");
					sb.append("'" + beginDate + "'");
					sb.append(" and ");
					sb.append("'" + endDate + "'");
				}
			}
			con = this.dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			this.turnResultSetToList(sellOrderList, rs);
		} 
		catch(Exception e){
			throw new Exception(e.getMessage());	
		}
		finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception ex) {
				throw new Exception(ex.getMessage());
			}
		}
		return sellOrderList;
}

	/**
	 * result turn list
	 * @param sellOrderList
	 * @param resultSet
	 * @return
	 * @throws Throwable
	 */
	public List<PSellSellorder> turnResultSetToList(List<PSellSellorder> sellOrderList,ResultSet resultSet) throws Throwable{
		while(resultSet.next()){
			PSellSellorder sellOrderModel = new PSellSellorder();
			sellOrderModel.setSellOrderId(resultSet.getInt("sellOrderId"));
			sellOrderModel.setCustomerID(resultSet.getInt("CustomerID"));
			sellOrderModel.setCreateDate(DateUtil.date2String(resultSet.getDate("CreateDate"),"yyyy-MM-dd"));
			sellOrderModel.setSellOrderCode(resultSet.getString("sellOrderCode"));
			sellOrderModel.setSourceBillType(resultSet.getInt("SourceBillType"));
			sellOrderModel.setCurrencyId(resultSet.getInt("currencyId"));
			sellOrderModel.setSellTypeId(resultSet.getInt("sellTypeId"));
			sellOrderModel.setBrief(resultSet.getString("Brief"));
			sellOrderModel.setDeliverymethodID(resultSet.getInt("DeliveryMethodID"));
			sellOrderModel.setDeliveryAddressId(resultSet.getInt("deliveryAddressId"));
			sellOrderModel.setSettlementId(resultSet.getInt("settlementId"));
			sellOrderModel.setSettleDate(DateUtil.date2String(resultSet.getDate("settleDate"),"yyyy-MM-dd"));
			sellOrderModel.setManagerID(resultSet.getInt("ManagerID"));
			sellOrderModel.setDepartmentID(resultSet.getInt("DepartmentID"));
			sellOrderModel.setBusinesserID(resultSet.getInt("BusinesserID"));
			sellOrderModel.setMakeBiller(resultSet.getString("MakeBiller"));
			sellOrderModel.setAuditinger(resultSet.getString("Auditinger"));
			sellOrderModel.setAuditingDate(DateUtil.date2String(resultSet.getDate("auditingDate"),"yyyy-MM-dd"));
			sellOrderModel.setIsAuditing(resultSet.getInt("IsAuditing"));
			sellOrderModel.setIsCancel(resultSet.getInt("IsCancel"));
			sellOrderModel.setIsClosed(resultSet.getInt("IsClosed"));
			sellOrderList.add(sellOrderModel);
		}
		return sellOrderList;
	}


	public ComboPooledDataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}



}